#region Imported Namespaces
using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Text;

using BO = PHSRAG.Insight21.BusinessObjects;
using PHSRAG.Insight21.InsightUtilities;
using DataAccess = PHSRAG.Utility.DataAccess;
using ICachePolicy = PHSRAG.Insight21.Policy.ICachePolicy;
using SearchCriteria = PHSRAG.Insight21.SearchContext.SearchCriteria;
using BudgetConstants = PHSRAG.Insight21.InsightUtilities.Constants.BudgetInfoConstants;
using AgreementsConstants = PHSRAG.Insight21.InsightUtilities.Constants.AgreementsConstants;
using PHSRAG.Insight21.SearchContext;
using GenerateKey = PHSRAG.Insight21.BusinessObjects.ModuleKey;
#endregion

namespace PHSRAG.Insight21.Agreements
{
	#region Class BudgetOverview
	/// <summary>
	/// The BudgetOverview class supports loading of all budget overview information
	/// for the logged in user and the given set of summary search criteria.
	/// </summary>
	public class BudgetOverview : BO.BusinessObject
	{
		#region Constants
		private static string BudgetOverviewKey			= GenerateKey.MakeKey("BudgetOverview_AACBE600-E296-4387-A0F7-AEABA1F6A0C3",GenerateKey.Agreement);
		private const string GetBudgetOverviewSPROCName	= "GetBudgetOverview";
		#endregion

		#region Enumerations (Private)
		private enum TableIndex
		{
			Category,
			Accounts,
			DirectCosts,
			IndirectCosts,
			Revenue,
			PriorYrBalance,
			Count
		}
		#endregion

		#region Instance Fields
		private int fundCount = -1;
		#endregion

		#region Class methods
		/// <summary>
		/// Compute the total costs and cash balance
		/// The formula used for calculating the cash balance is Total Revenue - Total Expenses
		/// The formula used for calculating the total cost is
		/// Budget - (Total Direct + Total Indirect Budget)
		/// Expense - (Total Direct Expense + Total Indirect Expense)
		/// Encumbrance - (Total Direct Encumbrance + Total Indirect Encumbrance)
		/// Balance - (Total Budget + Total Expense + Total Expense)
		/// </summary>
		/// <param name="dataContainer">The dataset with the data loaded for the direct cost,indirect cost,
		/// prior year balance, cash balance and revenue</param>
		public static void ComputeTotals(DataSet dataContainer)
		{
			DataRow directCost = dataContainer.Tables[AgreementsConstants.DirectCostsTableName].Rows[0];
			DataRow indirectCost = dataContainer.Tables[AgreementsConstants.IndirectCostsTableName].Rows[0];

			DataTable totalTable = dataContainer.Tables[AgreementsConstants.TotalCostsTableName];

			DataRow totalRow = totalTable.NewRow();

			totalRow[BudgetConstants.BudgetColumnName] = 
				Convert.ToDecimal(directCost[BudgetConstants.BudgetColumnName]) 
				+ Convert.ToDecimal(indirectCost[BudgetConstants.BudgetColumnName]);
			
			totalRow[BudgetConstants.ExpenseColumnName] = 
				Convert.ToDecimal(directCost[BudgetConstants.ExpenseColumnName]) 
				+ Convert.ToDecimal(indirectCost[BudgetConstants.ExpenseColumnName]);
			
			totalRow[BudgetConstants.EncumbranceColumnName] = 
				Convert.ToDecimal(directCost[BudgetConstants.EncumbranceColumnName]) 
				+ Convert.ToDecimal(indirectCost[BudgetConstants.EncumbranceColumnName]);
			
			totalRow[BudgetConstants.BalanceAvailableColumnName] = 
				Convert.ToDecimal(directCost[BudgetConstants.BalanceAvailableColumnName]) 
				+ Convert.ToDecimal(indirectCost[BudgetConstants.BalanceAvailableColumnName]);

			totalTable.Rows.Add(totalRow);

			DataRow priorYearBalance = dataContainer.Tables[AgreementsConstants.PriorYrBalanceTableName].Rows[0];

			DataTable cashBalanceTable = dataContainer.Tables[AgreementsConstants.CashBalanceTableName];

			DataRow cashBalanceRow = cashBalanceTable.NewRow();

			decimal revenue = Convert.ToDecimal(dataContainer.Tables[AgreementsConstants.RevenueTableName].Rows[0][0]);

			decimal cashBalance = 
				revenue + Convert.ToDecimal(priorYearBalance[0]) -
				Convert.ToDecimal(totalRow[BudgetConstants.ExpenseColumnName]);
			
			cashBalanceRow[BudgetConstants.ExpenseColumnName] = cashBalance;

			cashBalanceRow[BudgetConstants.BalanceAvailableColumnName] =
				cashBalance - Convert.ToDecimal(totalRow[BudgetConstants.EncumbranceColumnName]);

			cashBalanceTable.Rows.Add(cashBalanceRow);
		}
		#endregion

		#region Constructor
		/// <summary>
		/// Parameterized constructor, Initialize the base class with the active cache policy
		/// </summary>
		/// <param name="cachePolicy">Active cache policy</param>
		public BudgetOverview():base(null)
		{
		}
		#endregion

		#region Public properties
		/// <summary>
		/// Get the key to be used in all caching operations for this instance of the object.
		/// </summary>
		public static string CacheKey	{ get { return BudgetOverviewKey; } }

		
		#endregion

		#region Businessobject methods
		/// <summary>
		/// Load the current instance with budget overview data for the current user.
		/// </summary>
		/// <param name="connectionString">Database connection string</param>
		/// <param name="args">Is expected to contain atleast one argument - the domain user id</param>
		public override void Load(string connectionString, params object[] args)
		{
			try 
			{
				string sessionId = (string) args[0];
				SearchCriteria searchCriteria = (SearchCriteria) args[1];
				object domainUserID = searchCriteria[Constants.DomainUserIDKey];

				if (!(args[0] is string))
					throw new Exception("BudgetOverview load requires a session Id");

				if ((domainUserID == null) || ((string)domainUserID == string.Empty || (string)domainUserID == "0"))
					throw new Exception("BudgetOverview load requires a domain user ID");

				object includeProgramProjects = searchCriteria[Constants.IncludeProgramProjectsKey];

				bool includeDirect = (includeProgramProjects == null)?false:Convert.ToBoolean(includeProgramProjects);

				DataContainer = DataAccess.GetDataSet(connectionString, "GetBudgetOverview",
					DataAccess.BuildSqlParameterArray(
					"@sessionId",					SqlDbType.VarChar,	sessionId
					,"@domainUserId",				SqlDbType.Int,		Convert.ToInt32(domainUserID)
					,"@categoryId",					SqlDbType.Int,		searchCriteria[Constants.CategoryIDKey]
					,"@accountId",					SqlDbType.Int,		searchCriteria[Constants.AccountNumberKey]
					,"@includeProgramProjects",		SqlDbType.Bit,		searchCriteria[Constants.IncludeProgramProjectsKey]
					,"@fundNumber",					SqlDbType.VarChar,	searchCriteria[Constants.FundIDKey]
					,"@folderNumber",				SqlDbType.VarChar,	searchCriteria[Constants.FolderNumberKey]
					,"@bicsNumber",					SqlDbType.VarChar,	searchCriteria[Constants.BicsNumberKey]
					,"@walkerNumber",				SqlDbType.VarChar,	searchCriteria[Constants.WalkerNumberKey]
					,"@sponsorNumber",				SqlDbType.VarChar,	searchCriteria[Constants.SponsorNumberKey]
					,"@sponsorName",				SqlDbType.VarChar,	searchCriteria[Constants.SponsorNameKey]
					,"@sponsorTypeId",				SqlDbType.Int,		searchCriteria[Constants.SponsorTypeIDKey]
					,"@agreementTypeId",			SqlDbType.Int,		searchCriteria[Constants.AgreementTypeIDKey]
					,"@activityCode",				SqlDbType.VarChar,	searchCriteria[Constants.ActivityCodeKey]
					,"@locationId",					SqlDbType.Int,		searchCriteria[Constants.LocationIDKey]
					,"@budgetStartDate",			SqlDbType.DateTime, searchCriteria[Constants.BudgetStartDateKey]
					,"@budgetEndDate",				SqlDbType.DateTime, searchCriteria[Constants.BudgetEndDateKey]
					,"@projectStartDate",			SqlDbType.DateTime,	searchCriteria[Constants.ProjectStartDateKey]
					,"@projectEndDate",				SqlDbType.DateTime,	searchCriteria[Constants.ProjectEndDateKey]
					,"@piId",						SqlDbType.Int,		searchCriteria[Constants.PIIDKey]
					,"@chiefCodeList",				SqlDbType.VarChar,	searchCriteria.COSIds
					,"@sponsorGroupTypeId",			SqlDbType.Int,		searchCriteria[Constants.ViewingOptionKey]
					,"@budgetEndingDays",			SqlDbType.Int,		searchCriteria[Constants.DaysBudgetEndingKey]
					,"@projectEndingDays",			SqlDbType.Int,		searchCriteria[Constants.DaysProjectEndingKey]
					,"@fundCreatedDays",			SqlDbType.Int,		searchCriteria[Constants.DaysFundCreatedKey]
					,"@lowBalance",					SqlDbType.Decimal,	searchCriteria[Constants.BalanceLessThanKey]
					,"@highBalance",				SqlDbType.Decimal,	searchCriteria[Constants.BalanceGreaterThanKey]
					,"@fundStatusTypeId",			SqlDbType.Int,		searchCriteria[Constants.FundStatusIDKey]
					,"@agreementStatusTypeList",	SqlDbType.VarChar,	searchCriteria[Constants.AgreementStatusIDKey]
					,"@currentDateTime",			SqlDbType.DateTime,	null
					,"@glPostAsOfDate",				SqlDbType.DateTime,	searchCriteria[Constants.GLPostAsOfDateKey]
					,"@includeUserOrders",			SqlDbType.Bit,		searchCriteria[Constants.IncludeUserOrdersKey]
					));

				DataSet budgetOverviewData = DataContainer;

				if ( budgetOverviewData.Tables.Count != (int)TableIndex.Count )
					throw new Exception("Failed to load budget overview information.");
				else
				{
					budgetOverviewData.Tables[(int)TableIndex.Category].TableName	= 
						BudgetConstants.CategoryTableName;
					budgetOverviewData.Tables[(int)TableIndex.Accounts].TableName	= 
						AgreementsConstants.AccountsTableName;
					budgetOverviewData.Tables[(int)TableIndex.DirectCosts].TableName = 
						AgreementsConstants.DirectCostsTableName;
					budgetOverviewData.Tables[(int)TableIndex.IndirectCosts].TableName = 
						AgreementsConstants.IndirectCostsTableName;
					budgetOverviewData.Tables[(int)TableIndex.Revenue].TableName =	
						AgreementsConstants.RevenueTableName;					
					budgetOverviewData.Tables[(int)TableIndex.PriorYrBalance].TableName =	
						AgreementsConstants.PriorYrBalanceTableName;

					DataTable totalCostsTable = new DataTable(AgreementsConstants.TotalCostsTableName);
					AddCostsSchema(totalCostsTable);			
					budgetOverviewData.Tables.Add(totalCostsTable);
		
					DataTable cashBalanceTable = new DataTable(AgreementsConstants.CashBalanceTableName);
					AddCostsSchema(cashBalanceTable);
					budgetOverviewData.Tables.Add(cashBalanceTable);

					ComputeTotals(budgetOverviewData);
				}
			}
			catch (Exception ex)
			{
				throw new Exception(Utility.ExceptionReport.GetExceptionInfo(ex,false));
			}
		}

		#endregion

		#region Private methods
		/// <summary>
		/// Add the costs table schema
		/// </summary>
		/// <param name="dataTable">Datatable to which the new schema is to be added</param>
		private void AddCostsSchema(DataTable dataTable)
		{
			dataTable.Columns.Add
				(new DataColumn("Budget",typeof(System.Decimal)));
			dataTable.Columns.Add
				(new DataColumn("Expense",typeof(System.Decimal)));
			dataTable.Columns.Add
				(new DataColumn("Encumbrance",typeof(System.Decimal)));
			dataTable.Columns.Add
				(new DataColumn("BalanceAvailable",typeof(System.Decimal)));
		}
		#endregion

		#region Public methods
		/// <summary>
		/// No. of Funds included
		/// </summary>
		public int GetFundCount(string connectionString, string sessionId, int domainUserId)
		{
			if (fundCount == -1)
			{
				try
				{
					fundCount = Convert.ToInt32(DataAccess.ExecuteScalar(connectionString, "GetFundCount",
						DataAccess.BuildSqlParameterArray(
						"@sessionId",					SqlDbType.VarChar,	sessionId
						,"@domainUserId",				SqlDbType.Int,		domainUserId
						)));
				}
				catch (Exception ex)
				{
					throw new Exception(Utility.ExceptionReport.GetExceptionInfo(ex,false));
				}
			}
			return fundCount; 
		}
		#endregion
	}
	#endregion
}